using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace CubeTimer.Storage
{
  public class CubeTimesDB
  {
    private const string c_tableName = "CubeStats";
    private string m_cubeType;
    private readonly SQLiteConnection m_cnn;

    public CubeTimesDB(string connString)
    {
      m_cnn = new SQLiteConnection(connString);
    }

    public string CubeType
    {
      get { return m_cubeType; }
      set { m_cubeType = value; }
    }

    public void Open()
    {
      if (m_cnn.State != ConnectionState.Open)
        m_cnn.Open();
    }

    public void Close()
    {
      if (m_cnn.State == ConnectionState.Open)
        m_cnn.Close();
    }

    public void CreateTable()
    {
      using (DbCommand command = m_cnn.CreateCommand())
      {
        command.CommandText =
          "CREATE TABLE " + c_tableName + " (ID integer primary key autoincrement, WhenSolved DateTime, Ticks bigint, CubeType CHAR(5), Scramble CHAR(255))";
        command.ExecuteNonQuery();
      }
    }

    internal void DropTable(bool throwError)
    {
      string str = string.Empty;
      using (DbCommand command = m_cnn.CreateCommand())
      {
        try
        {
          command.CommandText = string.Format("DROP TABLE{1} [{0}]", c_tableName, !throwError ? " IF EXISTS" : "");
          command.ExecuteNonQuery();
        }
        catch (Exception exception)
        {
          if (throwError)
            str = str + string.Format("{0}\r\n", exception.Message);
        }
      }
      if (!string.IsNullOrEmpty(str))
      {
        throw new Exception(str);
      }
    }

    internal List<CubeTimeRow> Select(string query)
    {
      DataTable table = GetAsTable(query);
      return GetList(table.Rows);
    }

    private DataTable GetAsTable(string query)
    {
      DataTable table = new DataTable(c_tableName);
      using (SQLiteTransaction transaction = m_cnn.BeginTransaction())
      using (SQLiteDataAdapter adapter = new SQLiteDataAdapter())
      using (SQLiteCommand command = m_cnn.CreateCommand())
      {
        command.Transaction = transaction;
        command.CommandText = query;
        adapter.SelectCommand = command;

        try
        {
          adapter.Fill(table);
        }
        catch (SQLiteException ex)
        {
          if (ex.Message.Contains("no such table: " + c_tableName))
            CreateTable();
          else
            throw ex;
        }
      }
      return table;
    }

    internal object SelectSingle(string query)
    {
      using (SQLiteTransaction transaction = m_cnn.BeginTransaction())
      using (SQLiteCommand command = m_cnn.CreateCommand())
      {
        command.Transaction = transaction;
        command.CommandText = query;
        try
        {
          object scalar = command.ExecuteScalar();
          return scalar == DBNull.Value ? null : scalar;
        }
        catch (SQLiteException ex)
        {
          if (ex.Message.Contains("no such table: " + c_tableName))
            CreateTable();
          else
            throw ex;
        }
      }
      return null;
    }

    public int GetNumOfSolves()
    {
      string query = string.Format("select count(*) from {0} where CubeType='{1}';", c_tableName, m_cubeType);
      object num = SelectSingle(query);
      return num != null ? int.Parse(num.ToString()) : -1;
    }

    public int GetNumOfSolves(DateTime date)
    {
      string query = string.Format("select count(*) from {0} where WhenSolved >= '{1}' and CubeType ='{2}';", c_tableName, date.ToString("yyyy-MM-dd"), m_cubeType);
      object num = SelectSingle(query);
      return num != null ? int.Parse(num.ToString()) : -1;
    }

    public double GetPerOfSolves()
    {
      string query = string.Format("select * from {0} where CubeType='{1}';", c_tableName, m_cubeType);
      List<CubeTimeRow> rows = Select(query);
      DataTable numOdDays = GetChartData(rows);
      return rows.Count / numOdDays.Rows.Count;
    }

    public List<CubeTimeRow> GetTimes()
    {
      string query = string.Format("select * from {0}  where CubeType='{1}';", c_tableName, m_cubeType);
      return Select(query);
    }

    public int GetTimesCount(DateTime date)
    {
      string query = string.Format("select Count(Id) from {0} where WhenSolved >= '{1}' and CubeType='{2}' order by WhenSolved DESC;", c_tableName, date.ToString("yyyy-MM-dd"), m_cubeType);
      object single = SelectSingle(query);
      return int.Parse(single.ToString());
    }

    public List<CubeTimeRow> GetTimes(DateTime date)
    {
      string query = string.Format("select * from {0} where WhenSolved >= '{1}' and CubeType='{2}' order by WhenSolved DESC;", c_tableName, date.ToString("yyyy-MM-dd"), m_cubeType);
      return Select(query);
    }

    public List<CubeTimeRow> GetTimesExactly(DateTime date)
    {
      string query = string.Format("select * from {0} where (WhenSolved >= '{1}' and WhenSolved <= '{2}') and CubeType='{3}' order by WhenSolved DESC;", c_tableName, date.ToString("yyyy-MM-dd"), date.AddDays(1).ToString("yyyy-MM-dd"), m_cubeType);
      return Select(query);
    }

    public Dictionary<string, DateTime> GetDays()
    {
      string query = string.Format("select WhenSolved from {0} group by WhenSolved order by WhenSolved;", c_tableName);
      DataTable table = GetAsTable(query);
      Dictionary<string, DateTime> list = new Dictionary<string, DateTime>();
      foreach (DataRow row in table.Rows)
      {
        string key = ((DateTime) row[0]).ToShortDateString();
        DateTime value = (DateTime)row[0];
        if (!list.ContainsKey(key))
          list.Add(key, value);
      }
      return list;
    }

    public TimeSpan GetTotalBestTime()
    {
      List<CubeTimeRow> times = GetTimes();
      return GetBestTime(times);
    }

    public TimeSpan GetBestTime(DateTime date)
    {
      List<CubeTimeRow> times = GetTimes(date);
      return GetBestTime(times);
    }

    public List<CubeTimeRow> GetLastTimes(int num)
    {
      string query = string.Format("select * from {0} where CubeType='{1}' order by WhenSolved DESC limit {2};", c_tableName, m_cubeType, num);
      return Select(query);
    }

    public TimeSpan GetLastTimesAvg(int num)
    {
      List<CubeTimeRow> result = GetLastTimes(num);
      return GetAverage(result, num);
    }

    public TimeSpan GetTimesAvg()
    {
      string query = string.Format("select * from {0} where CubeType='{1}' order by WhenSolved DESC;", c_tableName, m_cubeType);
      List<CubeTimeRow> result = Select(query);
      return GetAverage(result, result.Count);
    }

    public TimeSpan GetTimesAvg(DateTime date)
    {
      List<CubeTimeRow> result = GetTimes(date);
      return GetAverage(result, result.Count);
    }

    public void AddTime(CubeTime time)
    {
      try
      {
        using (DbCommand command = m_cnn.CreateCommand())
        {
          command.CommandText = string.Format("INSERT INTO {0}(WhenSolved, Ticks, CubeType, Scramble) VALUES(@p1,@p2,@p3,@p4)", c_tableName);
          DbParameter parameter = command.CreateParameter();
          DbParameter parameter2 = command.CreateParameter();
          DbParameter parameter3 = command.CreateParameter();
          DbParameter parameter4 = command.CreateParameter();
          parameter.ParameterName = "@p1";
          parameter2.ParameterName = "@p2";
          parameter3.ParameterName = "@p3";
          parameter4.ParameterName = "@p4";
          parameter.Value = time.When;
          parameter2.Value = time.ElapsedTicks;
          parameter3.Value = time.CubeType;
          parameter4.Value = time.Scramble;
          command.Parameters.Add(parameter);
          command.Parameters.Add(parameter2);
          command.Parameters.Add(parameter3);
          command.Parameters.Add(parameter4);
          command.ExecuteNonQuery();
        }
      }
      catch (SQLiteException ex)
      {
        if (ex.Message.Contains("no such table: " + c_tableName))
          CreateTable();
      }
    }

    public void RemoveTime(CubeTime time)
    {
      using (DbCommand command = m_cnn.CreateCommand())
      {
        command.CommandText = string.Format("DELETE FROM {0} WHERE ID='{1}'", c_tableName, time.Id);
        command.ExecuteNonQuery();
      }
    }

    private static List<CubeTimeRow> GetList(DataRowCollection tableRows)
    {
      List<CubeTimeRow> rows = new List<CubeTimeRow>();
      if (tableRows != null)
        foreach (DataRow row in tableRows)
        {
//          if (TimeSpan.FromTicks((long) row[2]) != TimeSpan.MaxValue)
            rows.Add(new CubeTimeRow(row));
        }
      return rows;
    }

    public static DataTable GetChartData(List<CubeTimeRow> tableRows)
    {
      DataTable dt = new DataTable();
      DataColumn whenColumn = new DataColumn("When");
      whenColumn.DataType = typeof(DateTime);
      dt.Columns.Add(whenColumn);
      DataColumn timeColumn = new DataColumn("Time");
      timeColumn.DataType = typeof(long);
      dt.Columns.Add(timeColumn);
      DataColumn valuesColumn = new DataColumn("Values");
      valuesColumn.DataType = typeof(List<CubeTimeRow>);
      dt.Columns.Add(valuesColumn);
      Dictionary<DateTime, List<CubeTimeRow>> times = new Dictionary<DateTime, List<CubeTimeRow>>();
      foreach (CubeTimeRow timeRow in tableRows)
      {
        if (timeRow.Time == "DNF") continue;
        if (times.ContainsKey(timeRow.CubeTime.When.Date))
          times[timeRow.CubeTime.When.Date].Add(timeRow);
        else
        {
          List<CubeTimeRow> timesOfDay = new List<CubeTimeRow>();
          timesOfDay.Add(timeRow);
          times.Add(timeRow.CubeTime.When.Date, timesOfDay);
        }
      }
      foreach (KeyValuePair<DateTime, List<CubeTimeRow>> pair in times)
      {
        DataRow row = dt.NewRow();
        row["When"] = pair.Key;
        row["Time"] = GetAverage(pair.Value, pair.Value.Count).TotalSeconds;
        row["Values"] = pair.Value;
        dt.Rows.Add(row);
      }
      return dt;
    }

    public static TimeSpan GetAverage(List<CubeTimeRow> rows, int num)
    {
      if (num == 0) return TimeSpan.Zero;
      long ticks = 0;
      if (rows.Count >= num)
      {
        foreach (CubeTimeRow item in rows)
        {
          ticks += item.Ticks;
        }
      }
      return new TimeSpan(ticks / num);
    }

    public static TimeSpan GetBestTime(List<CubeTimeRow> times)
    {
      if (times.Count == 0) return new TimeSpan();
      long bestTime = new TimeSpan(9, 0, 0).Ticks;
      foreach (CubeTimeRow cubeTime in times)
        if (cubeTime.Ticks < bestTime)
          bestTime = cubeTime.Ticks;
      return TimeSpan.FromTicks(bestTime);
    }

  }
}